package com.supermap.mysql.service;

import java.util.HashMap;
import java.util.List;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.supermap.mysql.config.TargetDataSource;
import com.supermap.mysql.mybatis.MsqlMapper;
import com.supermap.mysql.tool.MysqlEntity;
import com.supermap.mysql.tool.Table;
import com.supermap.tool.StringUtil;

import net.sf.json.JSONObject;

/**
 * 
* @ClassName: TestaService 
* @Description: 动态切换不同的数据源
* 通过@TargetDataSource("ds1")注解 ds1为需要切换的数据源
* @author yuyao
* @date 2017年12月27日 下午5:12:33
 */
@Service
public class MsqlService {
	
	@Autowired
	MsqlMapper msqlMapper;
	
	/**
	 * 
	* @Title: querySqlDb1 
	* @Description: 在 ds1数据库查询数据
	* @param @param sql
	* @param @return    设定文件 
	* @return List<HashMap<String,Object>>    返回类型 
	* @throws
	 */
	@TargetDataSource("ds1")
    public HashMap<String, Object> querySqlDb1(String sql) {
		List<HashMap<String, Object>> list = msqlMapper.querySql(sql);
		return getQueryMap(list);
    }
	/**
	 * 
	* @Title: querySqlDb1 
	* @Description: 在 ds2数据库查询数据
	* @param @param sql
	* @param @return    设定文件 
	* @return List<HashMap<String,Object>>    返回类型 
	* @throws
	 */
	@TargetDataSource("ds2")
    public HashMap<String, Object> querySqlDb2(String sql) {
		List<HashMap<String, Object>> list = msqlMapper.querySql(sql);
		return getQueryMap(list);
    }
	
	/**
	 * 
	* @Title: querySqlDb1 
	* @Description: 在 ds3数据库查询数据
	* @param @param sql
	* @param @return    设定文件 
	* @return List<HashMap<String,Object>>    返回类型 
	* @throws
	 */
	@TargetDataSource("ds3")
    public HashMap<String, Object> querySqlDb3(String sql) {
		List<HashMap<String, Object>> list = msqlMapper.querySql(sql);
		return getQueryMap(list);
    }
	
	/**
	* @Title: getCx 
	* @Description: 更新数据 判断
	* @param @param sql
	* @param @return    设定文件 
	* @return HashMap<String,Object>    返回类型 
	* @throws
	 */
	public HashMap<String, Object> getCx(String sql) {
		int yxts = 0;
		if(sql.trim().toUpperCase().startsWith("update".toUpperCase())) {
			yxts = msqlMapper.updateSql(sql);
		} else if(sql.trim().toUpperCase().startsWith("insert".toUpperCase())) {
			yxts = msqlMapper.insertSql(sql);
		} 
		return getUpdateMap(yxts);
	}
	
	/**
	 * 
	* @Title: updateSqlDb1 
	* @Description: 修改 ds1 数据库 的数据
	* @param @param sql
	* @param @return    设定文件 
	* @return HashMap<String,Object>    返回类型 
	* @throws
	 */
	@TargetDataSource("ds1")
	public HashMap<String, Object> updateSqlDb1(String sql){
		 return getCx(sql);
	 };
	 
	 /**
		 * 
		* @Title: updateSqlDb1 
		* @Description: 修改 ds2 数据库 的数据
		* @param @param sql
		* @param @return    设定文件 
		* @return HashMap<String,Object>    返回类型 
		* @throws
		 */
	@TargetDataSource("ds2")
	public HashMap<String, Object> updateSqlDb2(String sql){
		 return getCx(sql);
	 };
		
	 /**
		 * 
		* @Title: updateSqlDb1 
		* @Description: 修改 ds3 数据库 的数据
		* @param @param sql
		* @param @return    设定文件 
		* @return HashMap<String,Object>    返回类型 
		* @throws
		 */
	@TargetDataSource("ds3")
	public HashMap<String, Object> updateSqlDb3(String sql){
		 return getCx(sql);
	};
	
	/**
	 * 
	* @Title: getUpdateMap 
	* @Description: 封装修改返回参数
	* @param @param size
	* @param @return    设定文件 
	* @return HashMap<String,Object>    返回类型 
	* @throws
	 */
	 public HashMap<String, Object> getUpdateMap(int size){
		 HashMap<String,Object> map = new HashMap<String,Object>();
		 map.put("code", "0");
		 map.put("msg", "Update successful");
		 map.put("size", size);
		 map.put("time", StringUtil.getSystemDateTimeString());
		 return map;
	 }
	 
	 /**
	  * 
	 * @Title: getMap 
	 * @Description:封装查询返回参数
	 * @param @param list
	 * @param @return    设定文件 
	 * @return HashMap<String,Object>    返回类型 
	 * @throws
	  */
	 public HashMap<String, Object> getQueryMap(List<HashMap<String, Object>> list){
		 HashMap<String,Object> map = new HashMap<String,Object>();
		 map.put("code", "0");
		 map.put("msg", "Query successful");
		 map.put("data", list);
		 map.put("size", list.size());
		 map.put("time", StringUtil.getSystemDateTimeString());
		 return map;
	 }
	 
	 /**
	 * @Title: getfastQuerySql 
	 * @Description: 快捷查询 SQL封装
	 * @param @param mysqlentity
	 * @param @param conditionValue
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	  */
	 public String getfastQuerySql(MysqlEntity mysqlentity) {
		 
	 	StringBuilder sql = new StringBuilder();
	 	
    	String cvSbs = getCvSql(mysqlentity);
    	
    	sql.append("select * from ");
    	sql.append(mysqlentity.getTableName());
    	if(!StringUtil.isNotNull(mysqlentity.getConditionValue())) {
    		sql.append(" where ");
        	sql.append(cvSbs);
    	}
    	sql.append(" limit 0,100");
	    	
		 return sql.toString();
	 }
	 
	 /**
	 * @Title: getFastInsertSql 
	 * @Description: 拼接添加语句
	 * @param @param mysqlentity
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	  */
	 public String getFastInsertSql(MysqlEntity mysqlentity) {
		 
		StringBuilder sql = new StringBuilder();
		
		String uvSbs = getIvSql(mysqlentity);
		
		 sql.append("INSERT INTO ");
		 sql.append(mysqlentity.getTableName());
		 sql.append(uvSbs);
		 
		 return sql.toString();
	 }
	 /**
	 * @Title: getFastUpdateSql 
	 * @Description: 快捷修改 SQL封装
	 * @param @param mysqlentity
	 * @param @param conditionValue
	 * @param @param updateValue
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	  */
	public String getFastUpdateSql(MysqlEntity mysqlentity) {
		 
		  StringBuilder sql = new StringBuilder();
			 
		  String uvSbs = getUvSql(mysqlentity);
		  String cvSbs = getCvSql(mysqlentity);
		 
		  sql.append("UPDATE ");
		  sql.append(mysqlentity.getTableName());
		  sql.append(" SET ");
		  sql.append(uvSbs);
		  sql.append(" where ");
		  sql.append(cvSbs);
		 
		 return sql.toString();
		 
	 }
	 /**
	 * @Title: getUvSql 
	 * @Description: 更新字段拼装
	 * @param @param mysqlentity
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	  */
	 @SuppressWarnings("unchecked")
	 public String getUvSql(MysqlEntity mysqlentity) {
		 
		StringBuilder uvSb = new StringBuilder();
			
		JSONObject uvjson = StringUtil.getJson(mysqlentity.getUpdateValue());
			
		Set<String> uvSet = uvjson.keySet();
			 
		 for(String uvv : uvSet) {
			 uvSb.append(uvv);
			 uvSb.append("='");
			 uvSb.append(uvjson.get(uvv) + "',");
		 }
		 
	  return uvSb.toString().substring(0, uvSb.length()-1);
	}
	 
	 /**
	 * @Title: getCvSql 
	 * @Description: 查询条件拼装
	 * @param @param mysqlentity
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	  */
	 @SuppressWarnings("unchecked")
	 public String getCvSql(MysqlEntity mysqlentity) {
		 
		 if(!StringUtil.isNotNull(mysqlentity.getConditionValue())) {
		 	return null;
		 }
		 
		 StringBuilder cvSb = new StringBuilder();
			
		JSONObject cvjson = StringUtil.getJson(mysqlentity.getConditionValue());
			
		 Set<String> cvSet = cvjson.keySet();
			 
		 for(String cvv : cvSet) {
			 cvSb.append(cvv);
			 cvSb.append("='");
			 cvSb.append(cvjson.get(cvv) + "' and ");
		 }
		 
	  return cvSb.toString().substring(0, cvSb.length()-4);
	}
	 
	 /**
	 * @Title: getIvSql 
	 * @Description: 添加 条件值拼装
	 * @param @param mysqlentity
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	  */
	 @SuppressWarnings("unchecked")
	public String getIvSql(MysqlEntity mysqlentity) {
		 
		 StringBuilder vNSb = new StringBuilder();
		 StringBuilder vSb = new StringBuilder();
			
		JSONObject uvjson = StringUtil.getJson(mysqlentity.getUpdateValue());
			
		Set<String> uvSet = uvjson.keySet();
			 
		 for(String uvv : uvSet) {
			 vNSb.append(uvv + ",");
			 vSb.append("'"+uvjson.get(uvv).toString().replace("'", "\"") + "',");
		 }
		  String vn = vNSb.toString().substring(0, vNSb.length()-1);
		  String v = vSb.toString().substring(0, vSb.length()-1);
		 
	  return " (" + vn + ") VALUES (" + v + ")";
	}
	 
	 /**
	 * @Title: findUserListSql 
	 * @Description: 表格查询
	 * @param @param mysqlentity
	 * @param @param table
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	  */
	 public String findTableListSql(MysqlEntity mysqlentity,Table table)  { 
		 
     	int ks = table.getLimit() * (table.getPage()-1);
     	//计算起始位置
     	if(ks < 0) {
     		ks = 0;
     	}
     	StringBuilder sql = new StringBuilder();
    	String cvSbs = getCvSql(mysqlentity);
    	sql.append("select * from ");
    	sql.append(mysqlentity.getTableName());
    	if(StringUtil.isNotNull(cvSbs)) {
    		sql.append(" where ");
        	sql.append(cvSbs);
    	}
    	if(StringUtil.isNotNull(table.getSortValue())) {
    		sql.append(" ORDER BY "+table.getSort()+" " + table.getSortValue());
    	}
    	sql.append(" limit "+ ks + "," + table.getLimit() + ";");
     	
     	return  sql.toString();
     }  
     /**
     * @Title: findUsercount 
     * @Description: 查询总数
     * @param @param mysqlentity
     * @param @param table
     * @param @return    设定文件 
     * @return String    返回类型 
     * @throws
      */
     public String findTableCount(MysqlEntity mysqlentity,Table table)  { 
     	
    	StringBuilder sql = new StringBuilder();
     	String cvSbs = getCvSql(mysqlentity);
     	sql.append("select count(1) count from ");
     	sql.append(mysqlentity.getTableName());
     	sql.append(" where ");
     	sql.append(cvSbs);
     	return  sql.toString();
     }
	 
	
}
